
--exec sproceHomeLoanCalculateMonthlyInstallmentResult 300000, 30, 1.078, 1.338, 1.388, 1.5
alter procedure sproceHomeLoanCalculateMonthlyInstallmentResult
	@loanamt float
	,@noofyear int
	,@rate1 float
	,@rate2 float
	,@rate3 float
	,@rate4 float
AS

--declare @rate1 float
--declare @rate2 float
--declare @rate3 float
--declare @rate4 float
--declare @noofyear int
--declare @loanamt float
--select @rate1 = (1.078/100)
--select @rate2 = (1.338/100)
--select @rate3 = (1.388/100)
--select @rate4 = (1.5/100)
--select @noofyear = 30
--select @loanamt = 300000

select @rate1 = (@rate1/100)
select @rate2 = (@rate2/100)
select @rate3 = (@rate3/100)
select @rate4 = (@rate4/100)

declare @totalperiod int
declare @curperiod int
declare @lastperiod int
declare @curyear int
declare @curinterestamt float
declare @outamt float
declare @pmt float
declare @principlpayment float
select @curperiod = 1
select @lastperiod = 1;
select @curyear = 1
select @totalperiod = @noofyear * 12
select @outamt = ROUND(@loanamt,2)
declare @output varchar(max)
set @output = '<br/><h1>Year ' + cast(@curyear as varchar) + '</h1>'
set @output = @output + '<table><th>Month</th><th>Outstanding Principle</th><th>Monthly Installment</th><th>Interest Payment</th><th>Principle Payment</th>'
WHILE(@curperiod <= @totalperiod)
BEGIN
	--select PMT = dbo.PMT( @rate1/12, 360 , 300000)
	
	
	IF @curyear = 1
		BEGIN
			if(@curperiod%12 = 1)
			begin
				select @pmt = ROUND(dbo.PMT(@rate1/12, @totalperiod , @outamt), 2)
			end 
			select @curinterestamt = ROUND((@outamt * @rate1)/12, 2)
		END
	ELSE IF @curyear = 2
		BEGIN
			if(@curperiod%12 = 1 and @curperiod = 13)
			begin
				select @lastperiod = (@curyear-1) * 12
				select @pmt = ROUND(dbo.PMT(@rate2/12, @totalperiod - @lastperiod , @outamt), 2)
			end
			--if(@curperiod = 24)
			--begin
			--	select @outamt [24 outamt], @curyear [24 cur year]
			--	break
			--end	
			select @curinterestamt = ROUND((@outamt * @rate2)/12, 2)
			--select ROUND((290797.51 * (1.338/100))/12, 2)
		END
	ELSE IF @curyear = 3
		BEGIN
			if(@curperiod%12 = 1 and @curperiod = 25)
			begin
				select @lastperiod = (@curyear-1) * 12
				select @pmt = ROUND(dbo.PMT(@rate3/12, @totalperiod - @lastperiod , @outamt), 2)
			end
			--if(@curperiod = 24)
			--begin
			--	select @outamt [24 outamt], @curyear [24 cur year]
			--	break
			--end	
			select @curinterestamt = ROUND((@outamt * @rate3)/12, 2)
		END
	ELSE IF @curyear > 3
		BEGIN
			if(@curperiod%12 = 1 and @curperiod = 37)
			begin
				select @lastperiod = (@curyear-1) * 12
				select @pmt = ROUND(dbo.PMT(@rate4/12, @totalperiod - @lastperiod , @outamt), 2)
			end
			
			
			select @curinterestamt = ROUND((@outamt * @rate4)/12, 2)
		END
		
	select @principlpayment = @pmt - @curinterestamt
	
	--select @curyear [Current Year]
	--select @curperiod [Month], @outamt [Outstanding Principle], @pmt [Monthly Installment], @curinterestamt [InterestPayment], @principlpayment [Principle Payment]
	
	set @output = @output + '<tr><td>' + CAST(@curperiod as varchar) + '</td><td>' + STR(@outamt, 13, 2) + '</td><td>' + CAST(@pmt as varchar) + '</td><td>' + CAST(@curinterestamt as varchar) + '</td><td>' + CAST(@principlpayment as varchar) + '</td></tr>';
	
	
	select @outamt = @outamt - @principlpayment
	--select @outamt
	if(@outamt < 1)
		break
	else
		if (@curperiod >= 12)
			begin
				select @curyear = (@curperiod/12) + 1 
				select @curperiod = @curperiod + 1
				if(@curperiod%12 = 1)
				begin
					set @output = @output + '</table><hr/><br/><h1>Year ' + cast(@curyear as varchar) + '</h1>'
					set @output = @output + '<table><th>Month</th><th>Outstanding Principle</th><th>Monthly Installment</th><th>Interest Payment</th><th>Principle Payment</th>'
				end
			end
		else
			begin
				select @curperiod = @curperiod + 1
				select @curyear = 1
			end
	
	
	
END

select @output
--select len(@output)